
package dao;

import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import vo.FuncionarioVO;

public class FuncionarioDAO {
    
    public static List<FuncionarioVO> getFuncionarios() {

        Connection con = ConnectionFactory.getConnection();

        List<FuncionarioVO> lista = new ArrayList<>();

        try {

            String query = "SELECT * FROM Funcionario";
            query += " order by nome";
            PreparedStatement stmt = con.prepareStatement(query);
            ResultSet res = stmt.executeQuery();

            while (res.next()) {
                String cpf = res.getString(1);
                String rg = res.getString(2);
                String nome = res.getString(3);
                Date data_nascimento = res.getDate(4);
                String cep = res.getString(5);
                String endereco = res.getString(6);
                int numero = res.getInt(7);
                String complemento = res.getString(8);
                String login = res.getString(9);
                String senha = res.getString(10);
                
                lista.add(new FuncionarioVO(cpf,rg,nome,data_nascimento,cep,endereco,numero,complemento,login,senha));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return lista;
    }
    
       public static List<FuncionarioVO> getFuncionarioPeloCPF(String paramCPF) {

        Connection con = ConnectionFactory.getConnection();

        List<FuncionarioVO> lista = new ArrayList<>();

        try {

            String query = "SELECT * FROM Funcionario WHERE cpf = ?";
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, paramCPF);
            ResultSet res = stmt.executeQuery();

            while (res.next()) {
                String cpf = res.getString(1);
                String rg = res.getString(2);
                String nome = res.getString(3);
                Date data_nascimento = res.getDate(4);
                String cep = res.getString(5);
                String endereco = res.getString(6);
                int numero = res.getInt(7);
                String complemento = res.getString(8);
                String login = res.getString(9);
                String senha = res.getString(10);
                
                lista.add(new FuncionarioVO(cpf,rg,nome,data_nascimento,cep,endereco,numero,complemento,login,senha));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return lista;
    }
       

    public static String getFuncionarioSenhaPorUsuario(String usuario) {

        Connection con = ConnectionFactory.getConnection();
        String senha = null;

        try {

            String query = "SELECT login,senha FROM Funcionario WHERE login = ?";
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1,usuario);
            ResultSet res = stmt.executeQuery();
        
            while (res.next()) {
               
                senha = res.getString(2);
            }
        } catch (Exception e) {
        }
        return senha;
    }
    public static void deleteFuncionarioPeloCPF(String cpf){
           
        Connection con= ConnectionFactory.getConnection();
        try {
            String query = "DELETE from Funcionario where cpf='"+cpf+"'" ;
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.executeUpdate();
        }
            catch (Exception e) {
            e.printStackTrace();
        }
        
    }
    public static boolean FuncionarioCadastrado(String Login)
    {

        Connection con = ConnectionFactory.getConnection();
        String login="..";
        try{
            String query = "Select * from Funcionario where login= ?";
            PreparedStatement stmt= con.prepareStatement(query);
            stmt.setString(1,Login);

            ResultSet res = stmt.executeQuery();
            while(res.next()){
            login=res.getString(9);
        
            if(Login.equals(login))
            {            

            return true;
            }}

        }catch(Exception e){
        
        
        }
        
        
       return false;       
    }
    
    public static boolean FuncionarioExiste(String CPF)
    {

        Connection con = ConnectionFactory.getConnection();
        String cpf="..";
        try{
            String query = "Select * from Funcionario where cpf= ?";
            PreparedStatement stmt= con.prepareStatement(query);
            stmt.setString(1,CPF);

            ResultSet res = stmt.executeQuery();
            while(res.next()){
            cpf=res.getString(1);
            
            if(CPF.equals(cpf))
            {            
            
            
            return true;
            }}

        }catch(Exception e){
        
        
        }
        
        
       return false;       
    }
    public static void alterarFuncionario(String cpf, String rg, String nome, String dataNascimento, String cep, String endereco, String numero, String complemento, String login, String senha)
    {
        Connection con=ConnectionFactory.getConnection();
        
        
        try{
            String query="Update Funcionario set rg=?,nome=?,data_Nascimento=?,cep=?,endereco=?,numero=?,complemento=?,login=?,senha=? where cpf=?";
            PreparedStatement stmt = con.prepareStatement(query);
            stmt.setString(1, rg);
            stmt.setString(2, nome);
            stmt.setString(3, dataNascimento);            
            stmt.setString(4, cep);            
            stmt.setString(5, endereco);
            stmt.setString(6, numero);           
            stmt.setString(7, complemento);            
            stmt.setString(8, login);                
            stmt.setString(9, senha);  
            stmt.setString(10, cpf);         
            
            stmt.executeUpdate();          
            
        }catch(Exception e){
            e.printStackTrace();
        }

    }
    
    public static void insertFuncionario(FuncionarioVO funcionario){
    
    Connection con = ConnectionFactory.getConnection();
        
        try {

            String query = "INSERT INTO Funcionario  VALUES(?,?,?,?,?,?,?,?,?,?)";
            PreparedStatement stmt = con.prepareStatement(query);
            
            stmt.setString(1,funcionario.getCpf());
            stmt.setString(2, funcionario.getRg());
            stmt.setString(3,funcionario.getNome());
            stmt.setDate(4,funcionario.getDataNascimento());
            stmt.setString(5,funcionario.getCep());
            stmt.setString(6, funcionario.getEndereco());
            stmt.setInt(7,funcionario.getNumero());
            stmt.setString(8, funcionario.getComplemento());
            stmt.setString(9,funcionario.getLogin());
            stmt.setString (10,funcionario.getSenha());
            stmt.executeUpdate();

           
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


    
}
